import pymysql
import pandas as pd
from utils import df_into_db


def read_sql(sql, db_name):
    db_config = {
        "charset": "utf8",
        "host": "192.168.0.114",
        "port": 3307,
        "user": "admin",
        "passwd": "123456"
    }
    conn = pymysql.connect(db=db_name, **db_config)
    return pd.read_sql(sql, conn)


def update_vol(frequency, datasource):
    df1 = read_sql(f"select * from k_line where symbol='BTC' and frequency='{frequency}' and datasource='{datasource}' "
                   f"order by datetime ",
                  db_name="all_history_ohlcvm_coinmarketcap")
    df1.drop(columns=['vol', 'amount'], inplace=True)
    df2 = read_sql(f"select datetime,vol,amount from k_line_with_vol where symbol='BTC' and frequency='{frequency}' "
                   f"and datasource='{datasource}' order by datetime ",
                  db_name="all_history_ohlcvm_coinmarketcap")
    print(f"len(df1):{len(df1)},len(df2):{len(df2)}")
    assert df1["datetime"].equals(df2["datetime"])
    df1 = pd.merge(df1, df2, on="datetime", how="left")
    db_config = {
        "charset": "utf8",
        "host": "192.168.0.114",
        "port": 3307,
        "user": "admin",
        "passwd": "123456"
    }
    conn = pymysql.connect(db="all_history_ohlcvm_coinmarketcap", **db_config)
    cursor = conn.cursor()
    data = [tuple(x) for x in df1[["vol", "amount", "id"]].values]
    update_sql = "update k_line set vol=%s,amount=%s where id=%s"
    affected_rows = cursor.executemany(update_sql, data)
    conn.commit()
    cursor.close()
    conn.close()
    print(f"成功更新 {affected_rows} 行")


def check_update(frequency, datasource):
    df1 = read_sql(f"select datetime,close,vol,amount from k_line where symbol='BTC' and frequency='{frequency}' and datasource='{datasource}' "
                   f"order by datetime ",
                  db_name="all_history_ohlcvm_coinmarketcap")
    df2 = read_sql(f"select datetime,close,vol,amount from k_line_with_vol where symbol='BTC' and frequency='{frequency}' "
                   f"and datasource='{datasource}' order by datetime ",
                  db_name="all_history_ohlcvm_coinmarketcap")
    for col in ["datetime", "close", "vol", "amount"]:
        assert df1[col].equals(df2[col])
        print(f"检查{col}成功")


def migrate_data(frequency, datasource):
    df = read_sql(f"select * from k_line_with_vol where symbol='BTC' and frequency='{frequency}' "
                   f"and datasource='{datasource}' order by datetime ", db_name="all_history_ohlcvm_coinmarketcap")
    df.drop(columns=['id'], inplace=True)
    df_into_db(df, db_name="all_history_ohlcvm_coinmarketcap", table_name="k_line")


if __name__ == '__main__':
    # update_vol("1h", "binance")
    # check_update("1h", "binance")
    # update_vol("1d", "binance")
    # check_update("1d", "binance")
    # update_vol("5m", "binance")
    # check_update("5m", "binance")
    # update_vol("5m", "okx")
    # check_update("5m", "okx")
    # update_vol("1d", "okx")
    # check_update("1d", "okx")
    # update_vol("1h", "okx")
    # check_update("1h", "okx")

    # update_vol("5m", "bitstamp")
    # check_update("5m", "bitstamp")

    # migrate_data("1d", "bitstamp")
    # check_update("1d", "bitstamp")

    migrate_data("1h", "bitstamp")
    check_update("1h", "bitstamp")